﻿CREATE TABLE [dbo].[DefinedEntityRelationships] (
    [ShortRelationshipName]       NVARCHAR (12)  NOT NULL,
    [RelationshipName]            NVARCHAR (50)  NOT NULL,
    [SuperRelationship]           NVARCHAR (12)  NULL,
    [ReverseRelationship]         NVARCHAR (50)  NULL,
    [Entity1Type]                 NVARCHAR (100) NOT NULL,
    [Entity2Type]                 NVARCHAR (100) NOT NULL,
    [MenuRelationshipName]        NVARCHAR (50)  NULL,
    [MenuReverseRelationshipName] NVARCHAR (50)  NULL,
    [E1_Read_Direct_E2]           INT            NULL,
    [E1_Read_Child_E2]            INT            NULL,
    [E1_Write_Direct_E2]          INT            NULL,
    [E1_Write_Child_E2]           INT            NULL,
    [E2_Read_Direct_E1]           INT            NULL,
    [E2_Read_Child_E1]            INT            NULL,
    [E2_Write_Direct_E1]          INT            NULL,
    [E2_Write_Child_E1]           INT            NULL,
    [RafikiNOSQL]                 NVARCHAR (MAX) NULL,
    [Notes]                       NVARCHAR (255) NULL,
    CONSTRAINT [PK_DefinedEntityRelationships] PRIMARY KEY CLUSTERED ([ShortRelationshipName] ASC),
    CONSTRAINT [FK_DefinedEntityRelationships_DefinedEntityRelationships] FOREIGN KEY ([SuperRelationship]) REFERENCES [dbo].[DefinedEntityRelationships] ([ShortRelationshipName]),
    CONSTRAINT [FK_DefinedEntityRelationships_DefinedEntityTypes] FOREIGN KEY ([Entity1Type]) REFERENCES [dbo].[DefinedEntityTypes] ([EntityType]) ON UPDATE CASCADE,
    CONSTRAINT [FK_DefinedEntityRelationships_DefinedEntityTypes1] FOREIGN KEY ([Entity2Type]) REFERENCES [dbo].[DefinedEntityTypes] ([EntityType]),
    CONSTRAINT [FK_DefinedEntityRelationships_DefinedSecurityLevels] FOREIGN KEY ([E1_Read_Direct_E2]) REFERENCES [dbo].[DefinedSecurityLevels] ([SecurityLevel]),
    CONSTRAINT [FK_DefinedEntityRelationships_DefinedSecurityLevels1] FOREIGN KEY ([E1_Read_Child_E2]) REFERENCES [dbo].[DefinedSecurityLevels] ([SecurityLevel]),
    CONSTRAINT [FK_DefinedEntityRelationships_DefinedSecurityLevels2] FOREIGN KEY ([E1_Write_Direct_E2]) REFERENCES [dbo].[DefinedSecurityLevels] ([SecurityLevel]),
    CONSTRAINT [FK_DefinedEntityRelationships_DefinedSecurityLevels3] FOREIGN KEY ([E1_Write_Child_E2]) REFERENCES [dbo].[DefinedSecurityLevels] ([SecurityLevel]),
    CONSTRAINT [FK_DefinedEntityRelationships_DefinedSecurityLevels4] FOREIGN KEY ([E2_Read_Direct_E1]) REFERENCES [dbo].[DefinedSecurityLevels] ([SecurityLevel]),
    CONSTRAINT [FK_DefinedEntityRelationships_DefinedSecurityLevels5] FOREIGN KEY ([E2_Write_Direct_E1]) REFERENCES [dbo].[DefinedSecurityLevels] ([SecurityLevel]),
    CONSTRAINT [FK_DefinedEntityRelationships_DefinedSecurityLevels6] FOREIGN KEY ([E2_Write_Child_E1]) REFERENCES [dbo].[DefinedSecurityLevels] ([SecurityLevel])
);












GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_DefinedEntityRelationships]
    ON [dbo].[DefinedEntityRelationships]([RelationshipName] ASC, [Entity1Type] ASC, [Entity2Type] ASC);


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'UNIQUE RELATIONSHIP PER SET OF ENTITIES', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'DefinedEntityRelationships', @level2type = N'INDEX', @level2name = N'IX_DefinedEntityRelationships';

